package com.budgetme.database;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Locale;

import android.content.ContentProvider;
import android.content.ContentUris;
import android.content.ContentValues;
import android.content.Context;
import android.content.UriMatcher;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.net.Uri;
import android.os.Bundle;
import android.text.TextUtils;
import android.util.Log;


// TODO: Need to test exceptions here

public class Provider extends ContentProvider {
    private static final String TAG = Provider.class.getName();;

	private DatabaseHelper mDatabaseHelper;
	
	private static final UriMatcher mURIMatcher = new UriMatcher(UriMatcher.NO_MATCH);
	
	private static final int BUCKETS = 100;
	private static final int BUCKETS_ID = 101;
	private static final int BUCKETS_BY_BUDGET_ID = 102;
	private static final int BUCKETS_CURRENT_BY_BUDGET_ID = 103;
	
	private static final int INCOME = 200;
	private static final int INCOME_ID = 201;
	private static final int INCOME_SUM = 202;
	
	private static final int EXPENSES = 300;
	private static final int EXPENSES_ID = 301;
	private static final int EXPENSES_BUDGET_ID = 302;
	private static final int EXPENSES_SUM_BUDGET_ID = 303;
	private static final int EXPENSES_BY_BUDGET = 304;
	private static final int EXPENSES_BY_BUDGET_LIFECYCLE = 305;
	
	private static final int BUDGETS = 400;
	private static final int BUDGETS_ID = 401;
	private static final int BUDGETS_FILTER = 402;
	private static final int BUDGETS_SUM = 403;
	private static final int BUDGETS_CURRENT_ID = 404;
	
	static {
		
		mURIMatcher.addURI(Database.AUTHORITY, Database.tbl_budgets.PATH, BUDGETS);										// Returns a list of all budgets
		mURIMatcher.addURI(Database.AUTHORITY, Database.tbl_budgets.PATH + "/#", BUDGETS_ID);							// Returns a single budget with ID #
		mURIMatcher.addURI(Database.AUTHORITY, Database.tbl_budgets.PATH + "/filter/#", BUDGETS_FILTER);				// Returns a list of all budgets filtered by enabled
		mURIMatcher.addURI(Database.AUTHORITY, Database.tbl_budgets.PATH + "/sum", BUDGETS_SUM);						// Returns the sum of all enabled budgets
		mURIMatcher.addURI(Database.AUTHORITY, Database.tbl_budgets.PATH + "/current/#", BUDGETS_CURRENT_ID);
		
//		mURIMatcher.addURI(Database.AUTHORITY, Database.tbl_buckets.PATH, BUCKETS);										// Returns bucket overview for all buckets
//		mURIMatcher.addURI(Database.AUTHORITY, Database.tbl_buckets.PATH + "/#", BUCKETS_ID);							// Returns bucket overview for bucket with ID #
//		mURIMatcher.addURI(Database.AUTHORITY, Database.tbl_buckets.PATH + "/budget/#", BUCKETS_BY_BUDGET_ID);			// Returns bucket overviews for each bucket for budget with ID #
//		mURIMatcher.addURI(Database.AUTHORITY, Database.tbl_buckets.PATH + "/current/#", BUCKETS_CURRENT_BY_BUDGET_ID);	// Returns bucket overview for current bucket for budget with ID #
		
		mURIMatcher.addURI(Database.AUTHORITY, Database.tbl_income.PATH, INCOME);
		mURIMatcher.addURI(Database.AUTHORITY, Database.tbl_income.PATH + "/#", INCOME_ID);
		mURIMatcher.addURI(Database.AUTHORITY, Database.tbl_income.PATH + "/sum", INCOME_SUM);							// Returns the sum of all incomes configured
		
		mURIMatcher.addURI(Database.AUTHORITY, Database.tbl_expenses.PATH, EXPENSES);
		mURIMatcher.addURI(Database.AUTHORITY, Database.tbl_expenses.PATH + "/#", EXPENSES_ID);
		mURIMatcher.addURI(Database.AUTHORITY, Database.tbl_expenses.PATH + "/budget", EXPENSES_BY_BUDGET);
		mURIMatcher.addURI(Database.AUTHORITY, Database.tbl_expenses.PATH + "/sum/#", EXPENSES_SUM_BUDGET_ID);
		mURIMatcher.addURI(Database.AUTHORITY, Database.tbl_expenses.PATH + "/budget/#", EXPENSES_BUDGET_ID);
		mURIMatcher.addURI(Database.AUTHORITY, Database.tbl_expenses.PATH + "/lifecycle/#", EXPENSES_BY_BUDGET_LIFECYCLE);
		
    }
	
	
	@Override
	public boolean onCreate() {
		mDatabaseHelper = new DatabaseHelper(getContext());
		return true;
	}
	
	
	/**
	 *  Uri uri						URI that was used to call the query
	 *  String[] projection			Which columns to return for the query
	 *  String selection			The "where" clause of the query, without the "where"
	 *  String[] selectionArgs		The arguments for the where clause (fills in ?'s)
	 *  String sortOrder			Sort order (<column> ASC|DESC)
	 */

	private int mBudgetId;
	private int mLifecycle;
	
	@Override
	public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) {
		Log.d(TAG, "Query received for URI: " + uri);
		

		SQLiteDatabase db = mDatabaseHelper.getReadableDatabase();
		
		String sql;
		
		SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
		
		int uriMatch = mURIMatcher.match(uri);
		switch(uriMatch) {
		
		
			case BUDGETS:
				Log.d(TAG, "Matched URI as " + BUDGETS);
				
				qb.setTables(Database.tbl_budgets.NAME);
				
				break;
				
			case BUDGETS_ID:
				Log.d(TAG, "Matched URI as " + BUDGETS_ID);
				
				qb.setTables(Database.tbl_budgets.NAME);
				qb.appendWhere(Database.tbl_budgets._id + "=" + uri.getLastPathSegment());
				
				break;
				
				
			case BUDGETS_FILTER:
				Log.d(TAG, "Matched URI as " + BUDGETS_FILTER);
				
				qb.setTables(Database.tbl_budgets.NAME);
				
				int filter = Integer.valueOf(uri.getLastPathSegment());
				
				switch (filter) {
					case Database.tbl_budgets.FILTER_ALL:
						break;
						
					case Database.tbl_budgets.FILTER_ENABLED:
						qb.appendWhere(Database.tbl_budgets.t_enabled + "=1");
						break;
						
					case Database.tbl_budgets.FILTER_DISABLED:
						qb.appendWhere(Database.tbl_budgets.t_enabled + "=0");
						break;
						
					default:
						throw new IllegalArgumentException("Unknown Filter: " + filter);
				}
				
				break;
				
			case BUDGETS_CURRENT_ID:
//				Log.d(TAG, "Matched URI as " + BUDGETS_CURRENT_ID);
//				
//				
//				select * from v_expenses_budget where e_date between "2013-07-06" AND "2013-07-18" AND b_id = 1 order by _id;
//				
//				
//				
//				int budgetId = Integer.valueOf(uri.getLastPathSegment());
//				
//				// Need to get the lifecycle of the budget that is being queried
//				Uri b_uri = Uri.withAppendedPath(Database.tbl_budgets.CONTENT_URI, String.valueOf(budgetId));
//				String[] b_projection = new String[] { Database.tbl_budgets._id, Database.tbl_budgets.t_lifecycle };
//				Cursor budget = query(b_uri, b_projection, null, null, null);
//				
//				int lifecycle = 0;
//				if (budget.moveToFirst()) {
//					lifecycle = budget.getInt(budget.getColumnIndex(Database.tbl_budgets.t_lifecycle));
//				} else {
//					return budget;
//				}
//				
//				String cycle = getCurrentCycle(lifecycle);
//				
//				projection = new String[] {
//						Database.tbl_buckets.NAME + "." + Database.tbl_buckets._id,
//						Database.tbl_budgets.t_name,
//						Database.tbl_budgets.t_budget,
//						Database.tbl_budgets.t_enabled,
//						Database.tbl_buckets.b_cycle,
//						"sum(" + Database.tbl_expenses.e_amount + ") as e_amount"
//				};
//				
//				sql = "SELECT " + TextUtils.join(", ", projection) + " FROM " + Database.tbl_budgets.NAME + 
//						" LEFT OUTER JOIN " + Database.tbl_budgets.NAME  + " ON " + Database.tbl_expenses.NAME + "." + Database.tbl_expenses.b_id  + "=" + Database.tbl_budgets.NAME + "." + Database.tbl_budgets._id +
//						" LEFT OUTER JOIN " + Database.tbl_expenses.NAME + " ON " + Database.tbl_expenses.b_id + "=" + Database.tbl_buckets.NAME + "." + Database.tbl_buckets._id +
//						" WHERE " + Database.tbl_buckets.t_id + "=" + budgetId + " AND " + Database.tbl_buckets.b_cycle + "='" + cycle + "'" +
//						" ORDER BY " + Database.tbl_buckets.NAME + "." + Database.tbl_buckets._id;
//
//				/**
//				 * SELECT tbl_buckets._id, t_name, t_budget, t_enabled, b_cycle, sum(e_amount) as e_amount FROM tbl_buckets 
//				 * LEFT OUTER JOIN tbl_budgets  ON t_id=tbl_budgets._id LEFT OUTER JOIN tbl_expenses ON b_id=tbl_buckets._id 
//				 * WHERE t_id=# AND b_cycle='<cycle>' GROUP BY tbl_buckets._id;
//				 * 
//				 */
//				
//				Log.d(TAG, "Executing SQL: " + sql);
//				Cursor current = db.rawQuery(sql, null);
//				
//				if (!(current.moveToFirst()) || !(current.getInt(budget.getColumnIndex(Database.tbl_buckets._id)) > 0)) {
//					
//					Log.d(TAG, "No current Bucket found for Budget.  Creating new Bucket for Budget ID: " + budgetId + " Cycle: " + cycle);
//					
//					// If there weren't any results then we need to create a good current bucket
//					ContentValues values = new ContentValues();
//					values.put(Database.tbl_buckets.t_id, budgetId);
//					values.put(Database.tbl_buckets.b_cycle, cycle);
//					
//					insert(uri, values);
//
//					current.close();
//
//					Log.d(TAG, "Executing SQL: " + sql);
//					current = db.rawQuery(sql, null);
//					
//				}
//				
//				return current;
		
//			case BUCKETS:
//				Log.d(TAG, "Matched URI as " + BUCKETS);
//				
//				qb.setTables(Database.tbl_buckets.NAME);
//				
//				break;
//				
//			case BUCKETS_ID:
//				Log.d(TAG, "Matched URI as " + BUCKETS_ID);
//				
//				qb.setTables(Database.tbl_buckets.NAME);
//				qb.appendWhere(Database.tbl_buckets._id + "=" + uri.getLastPathSegment());
//				
//				break;
				
			case EXPENSES:
				Log.d(TAG, "Matched URI as " + EXPENSES);
				
				qb.setTables(Database.tbl_expenses.NAME);
				
				break;
				
			case EXPENSES_ID:
				Log.d(TAG, "Matched URI as " + EXPENSES_ID);
				
				break;
			
			case EXPENSES_BUDGET_ID:
				Log.d(TAG, "Matched URI as " + EXPENSES_BUDGET_ID);
				
				qb.setTables(Database.tbl_expenses.NAME);
				qb.appendWhere(Database.tbl_expenses.b_id + "=" + uri.getLastPathSegment());
				
				break;
				
			case EXPENSES_BY_BUDGET_LIFECYCLE:
				Log.d(TAG, "Matched URI as " + EXPENSES_BY_BUDGET_LIFECYCLE);
				
				mBudgetId = Integer.valueOf(uri.getLastPathSegment());
				
				// Get budget lifecycle
				mLifecycle = getBudgetLifecycle(mBudgetId);
				
				// Get number of days for the cycle
				int offset = getLifecyleDays(mLifecycle);
				
				// Get the filter dates
				Calendar calendar = Calendar.getInstance();
				
				// Set up the formatting for the date we want to return
				SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm", Locale.US);
				
				String l_end = format.format(calendar.getTime());
				
				calendar.add(Calendar.DATE, -offset);
				String l_start = format.format(calendar.getTime());
				
				
				// Set up the query
				qb.setTables(Database.tbl_expenses.NAME);
				
				projection = new String[] {Database.tbl_expenses._id, Database.tbl_expenses.e_date, Database.tbl_expenses.e_amount, "'" + l_start + "' as l_start", "'" + l_end + "' as l_end"};
				
				qb.appendWhere(Database.tbl_expenses.b_id + "=" + mBudgetId);
				qb.appendWhere(" AND " + Database.tbl_expenses.e_date + " BETWEEN '" + l_start + "' AND '" + l_end + "'");
				
				
				break;
				
			case EXPENSES_SUM_BUDGET_ID:
				Log.d(TAG, "Matched URI as " + EXPENSES_SUM_BUDGET_ID);
				
				int mBudgetId = Integer.valueOf(uri.getLastPathSegment());
				

				// Need to get the lifecycle of the budget that is being queried
				mLifecycle = mLifecycle = getBudgetLifecycle(mBudgetId);
				
				String start_s = getCurrentCycle(mLifecycle);
				String end_s = getCycleEnd(start_s, mLifecycle); 
				
				qb.setTables(Database.tbl_expenses.NAME);
				qb.appendWhere(Database.tbl_expenses.b_id + "=" + mBudgetId);
				qb.appendWhere(" AND " + Database.tbl_expenses.e_date + " BETWEEN '" + start_s + "' AND '" + end_s + "'");

				projection = new String[] { "sum(" + Database.tbl_expenses.e_amount + ") as e_amount" };
				
				
				break;
				
			case EXPENSES_BY_BUDGET:
				Log.d(TAG, "Matched URI as " + EXPENSES_BY_BUDGET);
				
				qb.setTables(Database.v_expenses_budget.NAME);
				
				projection = new String[] {
						Database.tbl_expenses.NAME + "." + Database.tbl_expenses._id,
						Database.tbl_budgets.NAME  + "." + Database.tbl_budgets.t_name,
						Database.tbl_expenses.NAME + "." + Database.tbl_expenses.e_date,
						Database.tbl_expenses.NAME + "." + Database.tbl_expenses.e_amount
				};
								
				sql = "SELECT " + TextUtils.join(", ", projection) + " FROM " + Database.tbl_expenses.NAME + 
						" LEFT OUTER JOIN " + Database.tbl_budgets.NAME + " ON " + Database.tbl_budgets.NAME + "." + Database.tbl_budgets._id + "=" + Database.tbl_expenses.NAME + "." + Database.tbl_expenses.b_id;

				Log.d(TAG, "Executing SQL: " + sql);
				
				return db.rawQuery(sql, null);
				
				
			case INCOME:
				Log.d(TAG, "Matched URI as " + INCOME);
				
				qb.setTables(Database.tbl_income.NAME);
				
				break;
				
			case INCOME_ID:
				Log.d(TAG, "Matched URI as " + INCOME_ID);
				
				qb.setTables(Database.tbl_income.NAME);
				qb.appendWhere(Database.tbl_income._id + "=" + uri.getLastPathSegment());
				
				break;
				
			case INCOME_SUM:
				Log.d(TAG, "Matched URI as " + INCOME_SUM);
				
				qb.setTables(Database.tbl_income.NAME);
				
				projection = new String[] { "sum(" + Database.tbl_income.i_amount + ") as " + Database.tbl_income.i_amount };
				
				break;
				
			case BUDGETS_SUM:
				Log.d(TAG, "Matched URI as " + BUDGETS_SUM);
				
				qb.setTables(Database.tbl_budgets.NAME);
				qb.appendWhere(Database.tbl_budgets.t_enabled + "=1");
				
				projection = new String[] { "sum(" + Database.tbl_budgets.t_budget + ") as " + Database.tbl_budgets.t_budget };
				
				break;
				
			default:
				throw new IllegalArgumentException("Unknown URI " + uri);
				
		}
		
		
		Cursor cursor = qb.query(
	        	db,            	// The database to query
	        	projection,    	// The columns to return from the query
	        	selection,     	// The columns for the where clause
	        	selectionArgs, 	// The values for the where clause
	        	null,          	// don't group the rows
	        	null,          	// don't filter by row groups
	        	sortOrder    	// The sort order
	        );
		
		cursor.setNotificationUri(getContext().getContentResolver(), uri);
		return cursor;
		
	}
	



	@Override
	public Uri insert(Uri uri, ContentValues values) {
		Log.d(TAG, "Insert received for URI: " + uri);
		
        SQLiteDatabase db = mDatabaseHelper.getWritableDatabase();
        
        String table;

		int uriMatch = mURIMatcher.match(uri);
		switch(uriMatch) {
		
//			case BUCKETS:
//				Log.d(TAG, "Matched URI as " + BUCKETS);
//				
//				table = Database.tbl_buckets.NAME;
//				break;
				
			case EXPENSES:
				Log.d(TAG, "Matched URI as " + EXPENSES);
				
				table = Database.tbl_expenses.NAME;
				break;
				
			case INCOME:
				Log.d(TAG, "Matched URI as " + INCOME);
				
				table = Database.tbl_income.NAME;
				break;
				
//			case BUCKETS_CURRENT_BY_BUDGET_ID:
//				Log.d(TAG, "Matched URI as " + BUCKETS_CURRENT_BY_BUDGET_ID);
//				
//				table = Database.tbl_buckets.NAME;
//				break;
				
			default:
				throw new IllegalArgumentException("Unknown URI " + uri);
					
		}
        
		long rowId = db.insert(table, null, values);
		
		if (rowId > 0) {
			Uri insertUri = ContentUris.withAppendedId(uri, rowId);
			getContext().getContentResolver().notifyChange(insertUri, null);
			return insertUri;
		}
        
		throw new SQLException("Failed to insert row into " + uri);

	}


	@Override
	public int delete(Uri uri, String selection, String[] selectionArgs) {
		return 0;
	}

	@Override
	public String getType(Uri uri) {
		return null;
	}

	@Override
	public int update(Uri uri, ContentValues values, String selection, String[] selectionArgs) {
		return 0;
	}
	
	
	public class DatabaseHelper extends SQLiteOpenHelper {
		private String TAG = DatabaseHelper.class.getName();
		
		public static final String DB_NAME = "BudgetMe_Database";
		public static final int VERSION = 1;
		
		public DatabaseHelper(Context context) {
			super(context, DB_NAME, null, VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase mDatabase) {
			Log.d(TAG, "onCreate");
			
			Log.d(TAG, "Creating Database Tables");
			
			Log.d(TAG, "Executing SQL: " + Database.tbl_budgets.CREATE);
			mDatabase.execSQL(Database.tbl_budgets.CREATE);
			
//			Log.d(TAG, "Executing SQL: " + Database.tbl_buckets.CREATE);
//			mDatabase.execSQL(Database.tbl_buckets.CREATE);
			
			Log.d(TAG, "Executing SQL: " + Database.tbl_expenses.CREATE);
			mDatabase.execSQL(Database.tbl_expenses.CREATE);
			
			Log.d(TAG, "Executing SQL: " + Database.tbl_income.CREATE);
			mDatabase.execSQL(Database.tbl_income.CREATE);
			
//			Log.d(TAG, "Executing SQL: " + Database.v_expenses_budget.CREATE);
//			mDatabase.execSQL(Database.v_expenses_budget.CREATE);
			
			for (String insert : Database.PRELOAD) {
				Log.d(TAG, "Executing SQL: " + insert);
				mDatabase.execSQL(insert);
			}
			
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		}
		
		public void onOpen(SQLiteDatabase mDatabase) {
		}

	}
	
	private String getCurrentCycle(int lifecycle) {
		
		// Get a calendar instance to play with
		Calendar calendar = Calendar.getInstance();
		
		// Set up the formatting for the date we want to return
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd", Locale.US);
		
		// The offset to apply to the calendar to find the first day of the current cycle
		int offset = 0;
		
		
		switch (lifecycle) {
		
			case Database.tbl_budgets.LIFECYCLE_WEEKLY:
				
				// How many days ago was the first day of the current week (first day is 1, so 1 - 1 is 0, 0 means we don't change the date)
				offset = 1 - calendar.get(Calendar.DAY_OF_WEEK);
				
				break;
				
			case Database.tbl_budgets.LIFECYCLE_MONTHLY:
				
				// How many days ago was the first day of the current month (first day is 1, so 1 - 1 is 0, 0 means we don't change the date)
				offset = 1 - calendar.get(Calendar.DAY_OF_MONTH);
				
				break;
				
			case Database.tbl_budgets.LIFECYCLE_QUARTERLY:
				
				
				break;
				
			case Database.tbl_budgets.LIFECYCLE_YEARLY:
				
				// How many days ago was the first day of the current year (first day is 1, so 1 - 1 is 0, 0 means we don't change the date)
				offset = 1 - calendar.get(Calendar.DAY_OF_YEAR);
				
				break;
				
			default:
				// TODO: Figure out what to do here
				return null;
		}
		
		// Apply the offset
		calendar.add(Calendar.DATE, offset);
		
		// Format the date
		
		return format.format(calendar.getTime());	
	}
	
	private String getCycleEnd(String start, int lifecycle) {
		// Get a calendar instance to play with
		Calendar calendar = Calendar.getInstance();
		
		
		// Set up the formatting for the date we want to return
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		
		try {
			calendar.setTime(format.parse(start));
		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		// The offset to apply to the calendar to find the first day of the current cycle
		int offset = 0;
		
		// TODO: Test calendar.set for each of these:
		// calendar.set(Calendar.DAY_OF_YEAR, calendar.getActualMaximum(Calendar.DAY_OF_YEAR)) etc etc
		
		switch (lifecycle) {
		
			case Database.tbl_budgets.LIFECYCLE_WEEKLY:
				
				// Add 6 to get the last day of the week
				calendar.add(Calendar.DATE, 6);
				
				break;
				
			case Database.tbl_budgets.LIFECYCLE_MONTHLY:
				
				// Set date to the last day of the month
				calendar.set(Calendar.DAY_OF_MONTH, calendar.getActualMaximum(Calendar.DAY_OF_MONTH));  
				
				break;
				
			case Database.tbl_budgets.LIFECYCLE_QUARTERLY:
				
				
				break;
				
			case Database.tbl_budgets.LIFECYCLE_YEARLY:
				
				calendar.add(Calendar.DATE, calendar.getActualMaximum(Calendar.DAY_OF_YEAR) - 1);
				
				break;
				
			default:
				// TODO: Figure out what to do here
				return null;
		}
		// Format the date
		
		return format.format(calendar.getTime());
	}
	
	public static int getLifecyleDays(int lifecycle) {
		switch (lifecycle) {
		
			case Database.tbl_budgets.LIFECYCLE_WEEKLY:
				return 7;
			case Database.tbl_budgets.LIFECYCLE_MONTHLY:
				return 30;
			case Database.tbl_budgets.LIFECYCLE_QUARTERLY:
				return 90;
			case Database.tbl_budgets.LIFECYCLE_YEARLY:
				return 365;
			default:
				return 30;

		}
	}
	
	private int getBudgetLifecycle(int budgetId) {
		
		Uri b_uri = Uri.withAppendedPath(Database.tbl_budgets.CONTENT_URI, String.valueOf(budgetId));
		String[] b_projection = new String[] { Database.tbl_budgets._id, Database.tbl_budgets.t_lifecycle };
		Cursor budget = query(b_uri, b_projection, null, null, null);
		
		int lifecycle = 0;
		if (budget.moveToFirst()) {
			lifecycle = budget.getInt(budget.getColumnIndex(Database.tbl_budgets.t_lifecycle));
		}
		
		return lifecycle;
		
	}
	

}
